<?php
/**
 *
 * 使用教程
 * https://blog.csdn.net/qq_38044604/article/details/77771297
 */
require  "excel/Classes/PHPExcel.php";

class Excel
{
    const EXT = ".xls";
    const ROW = [
        "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "S", "Y", "Z",
    ];
    // 列头首个
    protected static $row_num = 1;
    // 当前索引到的列序号
    protected static $index = 0;

    /**
     * @var array $content 导入的数据内容
     */
    protected $content = array();

    /**
     * @var array $header 导入的数据头信息
     */
    protected $header = null;

    /**
     * @var PHPExcel $obj_PHPExcel
     */
    protected $obj_PHPExcel = null;
    /**
     * @var PHPExcel_Worksheet $excelSheet
     */
    protected $excelSheet = null;

    /**
     * @var PHPExcel_Writer_IWriter $obj_Writer
     */
    protected $obj_Writer = null;

    protected $fileName = null;

    public function __construct($sheet = "s1")
    {
//        ob_clean();
        $this->obj_PHPExcel = new PHPExcel();//实例化PHPExcel类 等同于新建一个Excel表格
        $this->excelSheet = $this->obj_PHPExcel->getActiveSheet(); //获得当前活动sheet的活动对象
        $this->excelSheet->setTitle($sheet);//设置当前活动Sheet名称
    }

    /**
     * 导出表格并下载
     * @param $header
     * @param $data
     * @param $file_name
     * @param $is_save_file
     * @param $file_path
     * @throws
     */
    public function export($header, $data, $file_name = "", $is_save_file = false, $file_path = "")
    {
        $this->matchContent($header, $data);
        $this->setHeaderRow($header);
        $this->setContentRow();

        $this->rowCenter();
        $this->createWriter();

        $this->fileName = date("Y-m-d")."_excel_".$file_name;

        if ($is_save_file) {
            $this->saveFile(empty($file_path) ? "excels/" : $file_path);
        }
        $this->output();
    }

    /**
     * 保存文件
     * @param $file_path
     * @throws PHPExcel_Writer_Exception
     */
    protected function saveFile($file_path)
    {
        $this->obj_Writer->save($file_path . $this->fileName.self::EXT);
    }

    /**
     * 直接输出浏览器
     * @throws PHPExcel_Writer_Exception
     */
    protected function output()
    {
        $this->browser_export($this->fileName, false);
        $this->obj_Writer->save('php://output');
    }

    /**
     * 创建工厂对象
     * @throws PHPExcel_Reader_Exception
     */
    protected function createWriter()
    {
        $this->obj_Writer = PHPExcel_IOFactory::createWriter($this->obj_PHPExcel, 'Excel2007');
        return $this->obj_Writer;
    }

    /**
     * 设置数据内容居中
     * @return PHPExcel_Worksheet
     * @throws PHPExcel_Exception
     */
    protected function rowCenter()
    {
        $this->excelSheet->getDefaultStyle()->getAlignment()//设置居中显示
        ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)//垂直居中
        ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中
        return $this->excelSheet;
    }

    /**
     * 使数据和头部信息对应匹配
     * @param $header
     * @param $content
     */
    protected function matchContent($header, $content)
    {
        foreach ($content as $index => $value) {
            foreach ($value as $k => $v) {
                if (!array_key_exists($k, $header)) {
                    unset($value[$k]);
                }
            }
            $row = array_merge($header, $value);
            $this->content[] = $row;
        }
    }

    /**
     * 设置列头
     * @param $header
     * @return string
     * @throws
     */
    protected function setHeaderRow($header)
    {
        foreach ($header as $field => $name) {
            $row = $this->setContentRowIndex();
            $this->excelSheet->setCellValue($row, $name);
            $this->excelSheet->getStyle($row)->getFont()->setBold(true);
            $this->excelSheet->getStyle($row)->getFont()->setSize(13);
        }
        $this->initContentNum();
        return $this->excelSheet;
    }


    /**
     * 设置列内容
     * @return PHPExcel_Worksheet
     */
    protected function setContentRow()
    {
        foreach ($this->content as $index => $value) {
            foreach ($value as $k => $v) {
                $this->excelSheet->setCellValue($this->setContentRowIndex(), $v);
            }
            $this->initContentNum();
        }
        return $this->excelSheet;
    }

    /**
     * 初始化行数
     */
    protected function initContentNum()
    {
        self::$row_num++;
        self::$index = 0;
    }

    /**
     * 设置内容行数
     * @return string
     */
    protected function setContentRowIndex()
    {
        $rowName = $this->getRowName(self::$index) . "" . (self::$row_num);
        self::$index++;
        return $rowName;
    }

    /**
     * 获取行dom
     * 例：A,B,C.....,AA,AB,AC....,BA,BC...
     * @param $index
     * @return mixed|string
     */
    private function getRowName($index)
    {
        $row_total = count(self::ROW);
        static $_index = 0;
        $row = "";
        if ($index >= $row_total) {
            $row = self::ROW[$_index % $row_total];
            $_index++;
            $i = $index - $row_total;
            if ($i >= $row_total) {
                $row = $this->getRowName($i);
            } else {
                $row .= self::ROW[$i];
            }
        } else {
            $row .= self::ROW[$index];
        }
        $_index = 0;
        return $row;
    }

    /**
     * 输出到浏览器 判断文件类型
     * @param $filename
     * @param string $type
     */
    protected function browser_export($filename, $type = 'Excel5')
    {
        // 将输出重定向到客户机的web浏览器(Excel5)
        if ($type == 'Excel5') {
            $ext = '.xls';
            header('Content-Type: application/vnd.ms-excel');//输出excel03文件
        } else {
            $ext = '.xlsx';
            header('Content-Type: application/vnd.openxmlformats-   officedocument.spreadsheetml.sheet');//输出excel07文件

        }
        header('Content-Disposition: attachment;filename="' . $filename . $ext . '"');//输出文件的名称
        header('Cache-Control: max-age=0');//禁止缓存
    }
}